package org.framework.common.ext.util.file;

import java.awt.Color;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.io.IOUtils;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.framework.common.ext.util.StringUtil;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;

/**
 * EXCEL工具类
 * <p>描述: </p>
 * <p>Company: Professional</p>
 * @author chanlong(陈龙)
 * @date 2016年9月8日 下午1:28:06
 * @version 1.0.2016
 */
public class ExcelUtil {
	
	private static int curRow;
	private static Map<String, Object> occupied;
	
	/**
	 * 导出Excel.
	 * @param name 表格名称
	 * @param rows 数据集
	 * @param params 参数(含表头信息)
	 * @author chanlong(陈龙)
	 * @date 2016年9月8日 下午1:27:02
	 */
	public static byte[] export(final HttpServletResponse response, final String name, final String html){
		curRow = 0;
		occupied = new HashMap<String, Object>();
		
		Document document = Jsoup.parse(html);
		Element table = document.getElementsByTag("table").get(0);
		Elements thead = table.getElementsByTag("thead");
		Elements tbody = table.getElementsByTag("tbody");
		Elements tfoot = table.getElementsByTag("tfoot");
		
		XSSFWorkbook book = new XSSFWorkbook();
		XSSFSheet sheet = book.createSheet(name);
		XSSFCellStyle titleStyle = getColumnStyle(book, true);
		XSSFCellStyle columnStyle = getColumnStyle(book, false);
		
		if(thead != null && !thead.isEmpty()) processElement(sheet, thead.get(0), XSSFCell.CELL_TYPE_STRING, titleStyle);
		if(tbody != null && !tbody.isEmpty()) processElement(sheet, tbody.get(0), XSSFCell.CELL_TYPE_BLANK, columnStyle);
		if(tfoot != null && !tfoot.isEmpty()) processElement(sheet, tfoot.get(0), XSSFCell.CELL_TYPE_STRING, titleStyle);
		
		ByteArrayOutputStream out = null;
		if(book != null){  
			try {
				out = new ByteArrayOutputStream(); 
				book.write(out);
			} catch (IOException e) {
				e.printStackTrace();
			} finally {
				IOUtils.closeQuietly(out);
			} 
        }  
		return out != null ? out.toByteArray() : new byte[]{};
	}
	
	/**
	 * 导出Excel.
	 * @param name 表格名称
	 * @param rows 数据集
	 * @param params 参数(含表头信息)
	 * @author chanlong(陈龙)
	 * @date 2016年9月8日 下午1:27:02
	 */
	public static byte[] export(final HttpServletResponse response, final String name, final List<Map<String,Object>> rows, final Map<String,Object> params){
		
		return null;
	}
	
	/*
	 * 处理html元素
	 */
	private static void processElement(final XSSFSheet sheet, final Element element, final int type, final XSSFCellStyle style){
		int rowIndex = 0;
		
		for(Element tr : element.select("tr")){
			int colIndex = 0;
			
			for(Element td : tr.select("th, td")){
				int colspan = 0, rowspan = 0;
				
				while (occupied.get(curRow+rowIndex + "_" + colIndex) != null) {
					++colIndex;
				}
				
				if(StringUtil.isNotEmpty(td.attr("rowspan"))){
					rowspan = Integer.valueOf(td.attr("rowspan"));
				}
				if(StringUtil.isNotEmpty(td.attr("colspan"))){
					colspan = Integer.valueOf(td.attr("colspan"));
				}
				
				if(td.hasAttr("rowspan") && td.hasAttr("colspan")){
					spanRowAndCol(sheet, td, curRow+rowIndex, colIndex, colspan, colspan, type, style);
					colIndex += colspan;
				}else if(td.hasAttr("colspan")){
					spanCol(sheet, td, curRow+rowIndex, colIndex, colspan, type, style);
					colIndex += colspan;
				}else if(td.hasAttr("rowspan")){
					spanRow(sheet, td, curRow+rowIndex, colIndex, rowspan, type, style);
					++colIndex;
				}else{
					createCell(td, getOrCreateRow(sheet, curRow+rowIndex), colIndex, type, style);
					++colIndex;
				}
			}
			++rowIndex;
		}
		
		curRow += rowIndex;
	}
	
	/*
	 * 创建单元格
	 */
	private static XSSFCell createCell(final Element td, final XSSFRow row, final int colIndex, final int type, final CellStyle style) {
    	XSSFCell cell = row.getCell(colIndex);
    	if (cell == null) {
    		cell = row.createCell(colIndex);
    	}
    	
    	// 设置单元格类型
    	cell.setCellType(XSSFCell.CELL_TYPE_STRING);
		
		// 为单元格赋值
    	switch (type) {
			case XSSFCell.CELL_TYPE_STRING:
				cell.setCellValue(new XSSFRichTextString(td.text()));
			break;
			default:
				cell.setCellValue(td.text());
			break;
		}
    	
    	// 设置单元格样式
		cell.setCellStyle(style);
		
    	return cell;
    }
	
	/*
	 * 合并行
	 */
	private static void spanRow(final XSSFSheet sheet, final Element td, final int rowIndex, final int colIndex, final int rowSpan, final int type, final CellStyle style) {
		// 合并
		mergeRegion(sheet, rowIndex, rowIndex + rowSpan - 1, colIndex, colIndex);
		
		// 创建单元格
		for (int i = 0; i < rowSpan; ++i) {
			XSSFRow row = getOrCreateRow(sheet, rowIndex + i);
			createCell(td, row, colIndex, type, style);
			occupied.put((rowIndex + i) + "_" + colIndex, true);
		}
	}
	
	/* 
	 * 合并列
	 */
	private static void spanCol(final XSSFSheet sheet, final Element td, final int rowIndex, final int colIndex, final int colSpan, final int type, final CellStyle style) {
    	// 合并
		mergeRegion(sheet, rowIndex, rowIndex, colIndex, colIndex + colSpan - 1);
		
		// 取行
    	XSSFRow row = getOrCreateRow(sheet, rowIndex);
    	
    	// 创建单元格
    	for (int i = 0; i < colSpan; ++i) {
    		createCell(td, row, colIndex + i, type, style);
    	}
    }
	
	/*
	 * 合并行列
	 */
	private static void spanRowAndCol(final XSSFSheet sheet, final Element td, final int rowIndex, final int colIndex, final int rowSpan, final int colSpan, final int type, final CellStyle style) {
		// 合并
		mergeRegion(sheet, rowIndex, rowIndex + rowSpan - 1, colIndex, colIndex + colSpan - 1);
    	
    	// 创建单元格
    	for (int i = 0; i < rowSpan; ++i) {
    		XSSFRow row = getOrCreateRow(sheet, rowIndex + i);
    		for (int j = 0; j < colSpan; ++j) {
    			createCell(td, row, colIndex + j, type, style);
    			occupied.put((rowIndex + i) + "_" + (colIndex + j), true);
    		}
    	}
    }
	
	/*
	 * 创建行对象
	 */
	private static XSSFRow getOrCreateRow(final XSSFSheet sheet, final int rowIndex) {
    	XSSFRow row = sheet.getRow(rowIndex);
    	if (row == null) row = sheet.createRow(rowIndex); 
	    return row;
    }

	/*
	 * 行列合并
	 */
	private static void mergeRegion(final XSSFSheet sheet, final int firstRow, final int lastRow, final int firstCol, final int lastCol) {
		sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
	}
	
    /*
     * 表格内容样式
     */
    public static XSSFCellStyle getColumnStyle(final XSSFWorkbook workbook, boolean istitle) {  
     	// 设置字体;
     	XSSFFont font = workbook.createFont(); 
     	// 设置表头
     	if(istitle){
     		//设置字体大小  
        	font.setFontHeightInPoints((short)12);  
        	//字体加粗  
        	font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
     	}
     	//设置字体名字   
     	font.setFontName("宋体");
     	
 		// 设置样式;
 		XSSFCellStyle style = workbook.createCellStyle();
 		// 设置顶边框;
 		style.setBorderTop(XSSFCellStyle.BORDER_THIN);
 		style.setTopBorderColor(color.get("black"));
 		// 设置左边框;
 		style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
 		style.setLeftBorderColor(color.get("black"));
 		// 设置右边框;
 		style.setBorderRight(XSSFCellStyle.BORDER_THIN);
 		style.setRightBorderColor(color.get("black"));
 		// 设置底边框;
 		style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
 		style.setBottomBorderColor(color.get("black"));
 		
 		// 在样式用应用设置的字体;
 		style.setFont(font);
 		// 设置自动换行;
 		style.setWrapText(false);
 		// 设置水平对齐的样式为居中对齐;
 		style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
 		// 设置垂直对齐的样式为居中对齐;
 		style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
 		
 		if(istitle){
 			style.setFillForegroundColor(color.get("gray"));
 			style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
 		}
 		
 		return style;
     }
    
    // 颜色字典
    private static Map<String, XSSFColor> color = new HashMap<String, XSSFColor>();
    static{
    	color.put("black", new XSSFColor(Color.BLACK));
    	color.put("gray", new XSSFColor(Color.LIGHT_GRAY));
    }
}
